import streamlit as st
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
import pymysql
from datetime import datetime

# 设置页面配置
st.set_page_config(
    page_title="乌克兰冲突数据分析大屏",
    page_icon="🇺🇦",
    layout="wide"
)

# 设置页面标题
st.title("🇺🇦 乌克兰冲突数据分析大屏")

# 数据库连接配置
# def get_db_connection():
#     return mysql.connector.connect(
#         host="localhost",
#         user="root",  # 请根据实际情况修改
#         password="123456",  # 请根据实际情况修改
#         database="ukraine_conflict"  # 请根据实际情况修改
#     )

def get_db_connection():
    return pymysql.connect(
        host="localhost",
        user="root",  # 请根据实际情况修改
        password="123456",  # 请根据实际情况修改
        database="ukraine_conflict",  # 请根据实际情况修改
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor
    )


# 获取数据
# def fetch_data(query):
#     conn = get_db_connection()
#     df = pd.read_sql(query, conn)
#     conn.close()
#     return df

def fetch_data(query):
    conn = get_db_connection()
    try:
        df = pd.read_sql(query, conn)
    finally:
        conn.close()
    return df


# 创建两列布局
col1, col2 = st.columns(2)

with col1:
    st.subheader("1. 每日事件数量统计")
    daily_events_query = """
    SELECT DATE(event_date) as date, COUNT(*) as count
    FROM russia_ukraine_conflict
    GROUP BY DATE(event_date)
    ORDER BY date
    """
    daily_events = fetch_data(daily_events_query)
    fig1 = px.line(daily_events, x='date', y='count',
                   title='每日事件数量趋势')
    st.plotly_chart(fig1, use_container_width=True)

    st.subheader("2. 事件类型分布")
    event_type_query = """
    SELECT event_type, COUNT(*) as count
    FROM russia_ukraine_conflict
    GROUP BY event_type
    ORDER BY count DESC
    LIMIT 10
    """
    event_types = fetch_data(event_type_query)
    fig2 = px.pie(event_types, values='count', names='event_type',
                  title='事件类型分布')
    st.plotly_chart(fig2, use_container_width=True)

    st.subheader("3. 地区事件分布")
    region_query = """
    SELECT region, COUNT(*) as count
    FROM russia_ukraine_conflict
    GROUP BY region
    ORDER BY count DESC
    LIMIT 10
    """
    regions = fetch_data(region_query)
    fig3 = px.bar(regions, x='region', y='count',
                  title='地区事件分布')
    st.plotly_chart(fig3, use_container_width=True)

with col2:
    st.subheader("4. 伤亡人数统计")
    casualties_query = """
    SELECT DATE(event_date) as date, 
           SUM(civilian_casualties) as civilian,
           SUM(military_casualties) as military
    FROM russia_ukraine_conflict
    GROUP BY DATE(event_date)
    ORDER BY date
    """
    casualties = fetch_data(casualties_query)
    fig4 = px.line(casualties, x='date', y=['civilian', 'military'],
                   title='伤亡人数趋势')
    st.plotly_chart(fig4, use_container_width=True)

    st.subheader("5. 事件严重程度分布")
    severity_query = """
    SELECT severity_level, COUNT(*) as count
    FROM russia_ukraine_conflict
    GROUP BY severity_level
    ORDER BY count DESC
    """
    severity = fetch_data(severity_query)
    fig5 = px.bar(severity, x='severity_level', y='count',
                  title='事件严重程度分布')
    st.plotly_chart(fig5, use_container_width=True)

    st.subheader("6. 月度事件统计")
    monthly_query = """
    SELECT DATE_FORMAT(event_date, '%Y-%m') as month,
           COUNT(*) as count
    FROM russia_ukraine_conflict
    GROUP BY DATE_FORMAT(event_date, '%Y-%m')
    ORDER BY month
    """
    monthly = fetch_data(monthly_query)
    fig6 = px.bar(monthly, x='month', y='count',
                  title='月度事件统计')
    st.plotly_chart(fig6, use_container_width=True)

# 添加页脚
st.markdown("---")
st.markdown("数据来源：Kaggle Ukraine Conflict Event Dataset")